﻿using LeisureNcre.Helper;
using LeisureNcre.Model;
using LeisureNrceCore.Common;
using LeisureNrceCore.Helper;
using LeisureNrceCore.Model;
using System;
using System.Data;
using System.Data.SQLite;

namespace LeisureNrceCore.Dao
{/// <summary>
 /// 功能描述    ：SQLiteHelper  
 /// 创 建 者    ：ly
 /// 创建日期    ：2021/2/18 21:22:57 
 /// </summary>
 /// <summary>
 /// sqlite操作辅助类
 /// </summary>
    public sealed class SQLiteDao
    {
        private static readonly object Locker = new object();

        private static SQLiteDao _instance;
        //连接字符串
        private readonly string _ConnectionStr = null;
        //数据库路径
        private readonly string _DBPATH = null;
        //连接对象
        private SQLiteConnection _SQLiteConn = null;
        //事务对象
        private SQLiteTransaction _SQLiteTrans = null;
        //事务运行标识
        private bool _IsRunTrans = false;

        //事务自动提交标识
        private bool _AutoCommit = false;

        /// <summary>
        /// 获取实例
        /// </summary>
        /// <returns></returns>
        public static SQLiteDao Init(String DBPath)
        {
            if (_instance == null)
            {
                lock (Locker)
                {
                    if (_instance == null)
                    {
                        _instance = new SQLiteDao(DBPath);
                    }
                }
            }
            return _instance;
        }

        private SQLiteDao(String DBPath)
        {
            _DBPATH = DBPath;
            if (!FileHelper.Exists(_DBPATH))
            {
                FileHelper.CreateDir(_DBPATH);
            }
            LogHelper.Info("数据库路径：" + _DBPATH);
            _ConnectionStr = string.Format("Data Source={0};Pooling=true", _DBPATH);

        }


        public bool IsNeedInit()
        {
            //判断是否存在文件
            if (!FileHelper.Exists(_DBPATH))
            {
                try
                {
                    SQLiteConnection.CreateFile(_DBPATH);
                    SQLiteConnection cnn = new SQLiteConnection(_ConnectionStr);
                    cnn.Open();
                    cnn.ChangePassword("!1234abc8765*");
                    cnn.Close();
                    return true;
                }
                catch (Exception e)
                {
                    throw new GlobalException("软件文件已损坏，请联系管理员或者重新安装", e);
                }

            }
            return false;
        }

        public SQLiteConnection GetConnection()
        {
            return _SQLiteConn;
        }

        /// <summary>
        /// 获取链接字符串
        /// </summary>
        /// <returns></returns>
        public string GetConnectionStr()
        {
            return _ConnectionStr;
        }

        /// <summary>
        /// 获取版本信息
        /// </summary>
        /// <returns></returns>
        public VersionInfo GetClientVersionInfo(int id)
        {
            try
            {
                OpenDb();
                SQLiteCommand cmd = _SQLiteConn.CreateCommand();
                cmd.CommandText = " select * from t_version where id =" + id;
                SQLiteDataReader reader = cmd.ExecuteReader();
                VersionInfo info = null;
                while (reader.Read())
                {
                    info = new VersionInfo
                    {
                        Id = reader.GetInt32(0),
                        VersionName = reader.GetString(1),
                        VersionCode = reader.GetString(2),
                        UpgradeType = reader.GetInt32(3),
                        AppDownloadUrl = reader.GetString(4),
                        Instructions = reader.GetString(5),
                        UpdatedDate = reader.GetDateTime(6)
                    };
                    break;
                }
                reader.Dispose();
                CloseDb();
                return info;
            }
            catch (Exception ex)
            {
                throw new GlobalException("查询客户版版本失败", ex);
            }
        }
        /// <summary>
        /// 获取用户信息
        /// </summary>
        /// <returns></returns>
        public UserInfo GetUserInfo(int id)
        {
            try
            {
                OpenDb();
                SQLiteCommand cmd = _SQLiteConn.CreateCommand();
                cmd.CommandText = " select * from t_user where id =" + id;
                SQLiteDataReader reader = cmd.ExecuteReader();
                UserInfo info = null;
                while (reader.Read())
                {
                    info = new UserInfo
                    {
                        Id = reader.GetInt32(0),
                        UserId = reader.GetInt64(1),
                        Name = reader.GetString(2),
                        Phone = reader.GetString(3),
                        Headimgurl = reader.GetString(4),
                        OpenId = reader.GetString(5),
                        UnionId = reader.GetString(6),
                        DeviceId = reader.GetString(7),
                        MacAddress = reader.GetString(8),
                        HardDisk = reader.GetString(9),
                        Cpu = reader.GetString(10),
                        ActivationDate = reader.GetDateTime(11),
                        Status = reader.GetInt32(12)
                    };
                    break;
                }
                reader.Dispose();
                CloseDb();
                return info;
            }
            catch (Exception ex)
            {
                throw new GlobalException("查询用户失败", ex);
            }
        }

        #region 增删改数据
        /// <summary>
        /// 增删改数据
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns></returns>
        public int OperationDB(string SQL)
        {

            try
            {
                LogHelper.Info("增删改数据,SQL:" + SQL);
                OpenDb();
                string Sql = string.Format(SQL);
                SQLiteCommand insertData = new SQLiteCommand(Sql, _SQLiteConn);
                int ret = insertData.ExecuteNonQuery();
                CloseDb();
                return ret;
            }
            catch (Exception ex)
            {
                LogHelper.Info("增删改数据,异常:" + ex.ToString());
                return 0;
            }
        }

        #endregion



        /// <summary>
        /// 打开当前数据库的连接
        /// </summary>
        /// <returns></returns>
        public bool OpenDb()
        {
            try
            {
                _SQLiteConn = new SQLiteConnection(_ConnectionStr);
                _SQLiteConn.SetPassword("!1234abc8765*");
                _SQLiteConn.Open();
                return true;
            }
            catch (Exception ex)
            {
                throw new GlobalException("打开数据库：" + this._ConnectionStr + "的连接失败：", ex);
            }
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void CloseDb()
        {
            if (_SQLiteConn != null && _SQLiteConn.State != ConnectionState.Closed)
            {
                if (_IsRunTrans && _AutoCommit)
                {
                    Commit();
                }
                _SQLiteConn.Close();
                _SQLiteConn = null;
            }
        }

        /// <summary>
        /// 开始数据库事务
        /// </summary>
        public void BeginTransaction()
        {
            _SQLiteTrans = _SQLiteConn.BeginTransaction();
            _IsRunTrans = true;
        }

        /// <summary>
        /// 开始数据库事务
        /// </summary>
        /// <param name="isoLevel">事务锁级别</param>
        public void BeginTransaction(IsolationLevel isoLevel)
        {
            _SQLiteTrans = _SQLiteConn.BeginTransaction(isoLevel);
            _IsRunTrans = true;
        }

        /// <summary>
        /// 提交当前挂起的事务
        /// </summary>
        public void Commit()
        {
            if (_IsRunTrans)
            {
                _SQLiteTrans.Commit();
                _IsRunTrans = false;
            }
        }

        /// <summary>
        /// 创建表
        /// </summary>
        /// <param name="dbPath">指定数据库文件</param>
        /// <param name="tableName">表名称</param>
        public void NewTable(string tableName, string tableColumnNames)
        {
            if (_SQLiteConn == null)
            {
                OpenDb();
            }
            if (_SQLiteConn.State != ConnectionState.Open)
            {
                _SQLiteConn.Open();

            }
            SQLiteCommand cmd = new SQLiteCommand
            {
                Connection = _SQLiteConn,
                // CommandText = "CREATE TABLE " + tableName + "(Name varchar,Team varchar, Number varchar)"
                CommandText = "CREATE TABLE " + tableName + tableColumnNames
            };
            cmd.ExecuteNonQuery();
            CloseDb();
        }

    }
}
